<?php

Class student_model extends CI_Model {

    public function __construct() {
        $this->load->database();
    }

    public function forChart($id,$level) {
        $this->db->select('s.title, date_format(p.date, "%M %e, %Y") as date, p.score', FALSE);
        $this->db->from(' progress p,story s,student_profile sp');
        $this->db->where('p.user_id', $id);
        $this->db->where('s.story_id = p.story_id');
        $this->db->where('s.level_number',$level);
        $this->db->where('p.user_id = sp.user_id');
        $this->db->order_by('p.progress_id', 'desc');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function getScores($id) {
        $this->db->select('l.colorhex, s.title,date_format(p.date,"%M %e, %Y") as date,p.duration,p.score', FALSE);
        $this->db->from('progress p,level l,story s');
        $this->db->where('p.user_id', $id);
        $this->db->where('s.story_id = p.story_id');
        $this->db->where('s.level_number = l.level_number');
        $this->db->order_by('p.progress_id', 'desc');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function getStudent($userid) {
        $this->db->select('sp.*,u.*,sn.section_name as section');
        $this->db->from('student_profile sp, user u,section sn');
        $this->db->where('u.user_id', $userid);
        $this->db->where('u.user_id = sp.user_id');
        $this->db->where('sn.section_id = sp.section_id');
        $query = $this->db->get();
        return $query->row_array();
    }

    public function updateStudent($id, $data) {
        $query = $this->db->where('user_id', $id);
        $query = $this->db->update('student_profile', $data);
        return $query;
    }

    public function updateUser($id, $data) {
        $query = $this->db->where('user_id', $id);
        $query = $this->db->update('user', $data);
        return $query;
    }

    public function addUser($data) {
        $this->db->insert('user', $data);
        $user_id = $this->db->insert_id();
        return $user_id;
    }

    public function getColor($level) {
        $this->db->select('colorhex');
        $this->db->from('level');
        $this->db->where('level_number', $level);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function getAllCriterias($userid) {
        $this->db->select('s.*,c.criteria_name');
        $this->db->from('status s,criteria c');
        $this->db->where('c.criteria_id = s.criteria_id');
        $this->db->where('s.user_id',$userid);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function getCriteria($state, $userid) {
        $this->db->select('s.criteria_id as id ,sp.level_number as level');
        $this->db->from('status s,student_profile sp');
        $this->db->where('s.state', $state);
        $this->db->where('s.doesExist', 'Y');
        $this->db->where('s.user_id = sp.user_id');
        $this->db->where('s.user_id', $userid);
        $this->db->order_by('RAND()');
        $query = $this->db->get();
        return $query->row_array();
    }

}

?>
